package cn.lfzj.dao;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import cn.lfzj.model.Book;

public class BookDao {
	private Connection conn;

	private void getConnection() {
		try {
			Class.forName("com.mysql.jdbc.Driver");
			String url = "jdbc:mysql://localhost:3306/test?characterEncoding=utf8";
			conn = DriverManager.getConnection(url, "root", "jisuanjixi");
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	private void close() {
		try {
			if (conn != null) {
				conn.close();
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	public List<Book> select(String bookName) {
		List<Book> list = new ArrayList<Book>();
		this.getConnection();
		try {
			String sql = "select * from books where bookname like ?";
			PreparedStatement ps = conn.prepareStatement(sql);
			ps.setString(1, "%" + bookName + "%");

			ResultSet rs = ps.executeQuery();

			Book book = null;
			while (rs.next()) {
				book = new Book();
				book.setId(rs.getInt("id"));
				book.setBookName(rs.getString("bookname"));
				book.setAuthor(rs.getString("author"));
				book.setPub(rs.getString("pub"));
				book.setPrice(rs.getDouble("price"));
				list.add(book);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		this.close();
		return list;
	}

	public Book selectById(int id) {
		this.getConnection();
		Book book = new Book();
		try {
			String sql = "select * from books where id = ?";
			PreparedStatement ps = conn.prepareStatement(sql);
			ps.setInt(1, id);
			ResultSet rs = ps.executeQuery();
			if (rs.next()) {
				book.setId(rs.getInt("id"));
				book.setBookName(rs.getString("bookname"));
				book.setAuthor(rs.getString("author"));
				book.setPub(rs.getString("pub"));
				book.setPrice(rs.getDouble("price"));
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		this.close();
		return book;
	}

	public int insert(Book book) {
		int result = 0;
		this.getConnection();
		try {
			String sql = "insert into books(bookname,author,pub,price) values(?,?,?,?)";
			PreparedStatement ps = conn.prepareStatement(sql);
			ps.setString(1, book.getBookName());
			ps.setString(2, book.getAuthor());
			ps.setString(3, book.getPub());
			ps.setDouble(4, book.getPrice());
			result = ps.executeUpdate();
		} catch (Exception e) {
			e.printStackTrace();
		}
		this.close();
		return result;
	}

	public int deleteById(int id) {
		int result = 0;
		this.getConnection();
		try {
			String sql = "delete from books where id=?";
			PreparedStatement ps = conn.prepareStatement(sql);
			ps.setInt(1, id);
			result = ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}

		this.close();
		return result;
	}

	public int update(Book book) {
		int result = 0;
		this.getConnection();
		try {
			String sql = "update books set bookname=?,author=?,pub=?,price=? where id=?";
			PreparedStatement ps = conn.prepareStatement(sql);
			ps.setString(1, book.getBookName());
			ps.setString(2, book.getAuthor());
			ps.setString(3, book.getPub());
			ps.setDouble(4, book.getPrice());
			ps.setInt(5, book.getId());
			result = ps.executeUpdate();
		} catch (Exception e) {
			e.printStackTrace();
		}
		this.close();
		return result;
	}

	public int delete(String[] ids) {
		int result = 0;
		this.getConnection();
		try {
			String str = "";
			for (String i : ids) {
				str = str + i + ",";
			}
			str = str.substring(0, str.length() - 1);
			String sql = "delete from books where id in(" + str + ")";
			PreparedStatement ps = conn.prepareStatement(sql);
			result = ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}

		this.close();
		return result;
	}
}
